package net.swa.util;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;


/**
 * 导出Excel类
 * @author Carlot
 *
 */
public class ImportExcel {
	
	
	//表标题
	public static final String tablHeader = "南京市文化产业发展专项资金评审意见表";
	
	//表头     
	public static final String[] tableTitle = {"类型","序号","申请单位","项目（产品）名称","报送单位","申请金额（万元）","补助金额   （万元）","评审意见"};
	 
	//排除表头和标题的开始行数
	public static final int beginRow = 2;
	 
	//创建 HSSFWorkbook
	public static HSSFWorkbook workbook = new HSSFWorkbook();     
	
	 //创建HSSFSheet     
	public static HSSFSheet sheet  = workbook.createSheet("审评意见");     
	
    
    /**
     * 创建表头
     */
    public static void createTableHeader()
    {
    	HSSFRow rowHeader = sheet.createRow(0);
    	rowHeader.setHeight((short)500);
    	HSSFCellStyle style = workbook.createCellStyle();
    	HSSFFont font = workbook.createFont();
    	font.setFontName("华文中宋");
    	font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    	font.setFontHeightInPoints((short)16);
    	style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    	style.setBorderTop(HSSFCellStyle.BORDER_THIN);
    	style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    	style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    	style.setBorderRight(HSSFCellStyle.BORDER_THIN);
    	style.setFont(font);
    	style.setWrapText(true);
    	
    	HSSFCell cellHeader = rowHeader.createCell(0);
    	cellHeader.setCellValue(tablHeader);
    	cellHeader.setCellStyle(style);
    }
    
    
    /***
     * 创建表标题
     */
    public static void createTableTitle()
    {
    	HSSFRow rowTitle = sheet.createRow(1);
    	rowTitle.setHeight((short)400);
    	HSSFCellStyle style = workbook.createCellStyle();
    	style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    	HSSFFont font = workbook.createFont();
    	font.setFontName("微软雅黑");
    	font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    	font.setFontHeightInPoints((short)12);
    	style.setBorderTop(HSSFCellStyle.BORDER_THIN);
    	style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    	style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    	style.setBorderRight(HSSFCellStyle.BORDER_THIN);
    	style.setFont(font);
    	style.setWrapText(true);
    	
    	for(int i = 0; i < tableTitle.length; i++)
    	{
    		String s = tableTitle[i];
    		
    		HSSFCell cellTitle = rowTitle.createCell(i);
    		cellTitle = rowTitle.createCell(i);
        	cellTitle.setCellValue(s);
        	cellTitle.setCellStyle(style);
    	}
    }
    
    
    
    /**
     * 创建表尾
     * @param sqMoney
     * @param bzMoney
     * @param rowNum
     * @param size
     */
	public static void createTableBottom(float sqMoney, float bzMoney, int rowNum, int size)
    {
    	/**
    	 * 第四行样式
    	 */
    	HSSFCellStyle style = workbook.createCellStyle();
    	style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    	style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    	HSSFFont font = workbook.createFont();
    	font.setFontName("微软雅黑");
    	font.setFontHeight((short)220);
    	style.setBorderTop(HSSFCellStyle.BORDER_THIN);
    	style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    	style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    	style.setBorderRight(HSSFCellStyle.BORDER_THIN);
    	style.setFont(font);
    	style.setWrapText(true);
    	
    	/** 第四行 */
    	HSSFRow rowBottom = sheet.createRow(rowNum);
    	rowBottom.setHeight((short)420);
    	HSSFCell cellBottom= rowBottom.createCell(0);
    	cellBottom.setCellValue("共计");
    	cellBottom.setCellStyle(style);
    	
    	for(int i = 1; i < tableTitle.length; i++)
    	{
    		cellBottom = rowBottom.createCell(i);
    		
    		if(i == 1)
    		{
    			cellBottom.setCellValue(size);
    	    	cellBottom.setCellStyle(style);
    		}
    		else if(i == 5)
    		{
    			cellBottom.setCellValue(sqMoney);
    	    	cellBottom.setCellStyle(style);
    		}
    		else if(i == 6)
    		{
    			cellBottom.setCellValue(bzMoney);
    	    	cellBottom.setCellStyle(style);
    		}
    		else
    		{
    			cellBottom.setCellValue("");
    	    	cellBottom.setCellStyle(style);
    		}
    	}
    	
    	/** 结束 */
    	//CellRangeAddress()中的一个构造函数含有四个参数，分别代表起始行、结束行、结束列、结束列
    	sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 2, 4));
    	sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));
    	sheet.setColumnWidth(0, 5000);
    	sheet.autoSizeColumn((short)1); 
    	sheet.autoSizeColumn((short)2); 
    	sheet.autoSizeColumn((short)3); 
    	sheet.autoSizeColumn((short)4); 
    	sheet.autoSizeColumn((short)5); 
    	sheet.autoSizeColumn((short)6); 
    	sheet.autoSizeColumn((short)7); 
    }
	
	
	/**
	 * 填充数据
	 * @param s
	 * @param rowData
	 * @param style
	 * @return
	 */
	
	
	/**
	 * 合并各类的单元格
	 * @param arrType
	 */
	public static void mergedRegion(int[] arrType)
	{
		//开始标识，用于判断是否是第一次
		boolean flag = true;
    	
    	//当前行数
    	int currentRow = 0;
    	
    	//循环数组
    	for(int i = 0; i < arrType.length; i++)
    	{
    		//第一次加载 
    		if(flag)
    		{
    			//如果不的等于0,就把开始的类别数字+1,开始合并第一行
	    		if(arrType[i] != 0)
	    		{
	    			arrType[i]++;
	    			
	    			sheet.addMergedRegion(new CellRangeAddress(beginRow, arrType[i], 0, 0));
		    		
		    		flag = false;
	    		}
    		}
    		//合并后面的类别,开始行数=当前行数+1,结束行数=当前行数+当前类别的行数;
    		else
    		{
	    		if(arrType[i] != 0)
	    		{
	        		sheet.addMergedRegion(new CellRangeAddress(currentRow + 1, currentRow + arrType[i], 0, 0));
	    		}
    		}
    		
    		//记录当前行数
    		currentRow += arrType[i];
    	}
	}
}
